package com.ruoyi.common.utils.excel;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelPersonDataReader {
    public static void main(String[] args) {
        String filePath = "C:\\Users\\farben\\Desktop\\excel\\表格数据.xlsx";

        try {
            // 读取Excel文件
            List<PersonData> personDataList = readExcelData(filePath);

            // 示例查询
            //            System.out.println("=== 查询示例 ===");
            // 查询指定人员的数据
//            String targetPerson = "孙鸿历";
//            System.out.println("\n查询人员: " + targetPerson);
//            displayPersonData(personDataList, targetPerson);

            // 查询所有人员的数据
            System.out.println("\n=== 所有人员数据 ===");
            for (PersonData person : personDataList) {
                displayPersonData(personDataList, person.getUserName());
            }

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 读取Excel数据
     */
    public static List<PersonData> readExcelData(String filePath) throws IOException {
        List<PersonData> personDataList = new ArrayList<>();

        try (FileInputStream file = new FileInputStream(filePath);
             Workbook workbook = new XSSFWorkbook(file)) {

            Sheet sheet = workbook.getSheetAt(0); // 第一个工作表

            // 读取表头，获取日期列
            Row headerRow = sheet.getRow(0);
            Map<Integer, String> dateColumnMap = getDateColumns(headerRow);

            // 从第二行开始读取数据（跳过表头）
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null || isSummaryRow(row)) {
                    continue; // 跳过空行和汇总行
                }

                PersonData personData = parsePersonData(row, dateColumnMap);
                if (personData != null) {
                    personDataList.add(personData);
                }
            }
        }

        return personDataList;
    }

    /**
     * 读取Excel数据
     */
    public static List<PersonData> readExcelData(InputStream inputStream) throws IOException {
        List<PersonData> personDataList = new ArrayList<>();
        Workbook workbook = ExcelProcess.checkExcel(inputStream);
        Sheet sheet = workbook.getSheetAt(0); // 第一个工作表
        // 读取表头，获取日期列
        Row headerRow = sheet.getRow(1);
        Map<Integer, String> dateColumnMap = getDateColumns(headerRow);

        // 从第二行开始读取数据（跳过表头）
        for (int i = 2; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row == null || isSummaryRow(row)) {
                continue; // 跳过空行和汇总行
            }

            PersonData personData = parsePersonData(row, dateColumnMap);
            if (personData != null) {
                personDataList.add(personData);
            }
        }
        return personDataList;
    }

    /**
     * 获取日期列映射（列索引 -> 日期字符串）
     */
    private static Map<Integer, String> getDateColumns(Row headerRow) {
        Map<Integer, String> dateColumnMap = new HashMap<>();

        for (int i = 9; i < headerRow.getLastCellNum(); i++) { // 从第10列(I)开始是日期
            Cell cell = headerRow.getCell(i);
            if (cell != null) {
                String cellValue = getCellValueAsString(cell);
                if (cellValue != null && !cellValue.trim().isEmpty()) {
                    dateColumnMap.put(i, cellValue.trim());
                }
            }
        }

        return dateColumnMap;
    }

    /**
     * 解析人员数据
     */
    private static PersonData parsePersonData(Row row, Map<Integer, String> dateColumnMap) {
        try {
            PersonData personData = new PersonData();

            // 基本信息
            personData.setSerialNumber(getCellValueAsString(row.getCell(0))); // A列
            personData.setCity(getCellValueAsString(row.getCell(1))); // B列
            personData.setRegion(getCellValueAsString(row.getCell(2))); // C列
            personData.setBusinessLeader(getCellValueAsString(row.getCell(3))); // D列
            personData.setUserName(getCellValueAsString(row.getCell(4))); // E列
            personData.setPersonStatus(getCellValueAsString(row.getCell(5))); // F列
            personData.setPreviousBusiness(getCellValueAsString(row.getCell(6))); // G列
            personData.setCurrentBusiness(getCellValueAsString(row.getCell(7))); // H列
            personData.setOtherBusiness(getCellValueAsString(row.getCell(8))); // I列

            // 日期数据
            Map<String, String> dateData = new LinkedHashMap<>();
            for (Map.Entry<Integer, String> entry : dateColumnMap.entrySet()) {
                int columnIndex = entry.getKey();
                String date = entry.getValue();
                String value = getCellValueAsString(row.getCell(columnIndex));
                dateData.put(date, value);
            }
            personData.setDateData(dateData);

            return personData;

        } catch (Exception e) {
            System.err.println("解析行数据时出错: " + e.getMessage());
            return null;
        }
    }

    /**
     * 获取单元格值的字符串表示
     */
    private static String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return "";
        }

        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return new SimpleDateFormat("yyyy-MM-dd").format(cell.getDateCellValue());
                } else {
                    return String.valueOf((int) cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }

    /**
     * 判断是否为汇总行
     */
    private static boolean isSummaryRow(Row row) {
        Cell firstCell = row.getCell(0);
        if (firstCell == null) return true;

        String firstCellValue = getCellValueAsString(firstCell);
        return firstCellValue.isEmpty() ||
                firstCellValue.equals("投入") ||
                firstCellValue.equals("事假") ||
                firstCellValue.equals("休假") ||
                firstCellValue.equals("离职/离项") ||
                firstCellValue.equals("总数") ||
                firstCellValue.equals("DH") ||
                firstCellValue.equals("长尾游戏") ||
                firstCellValue.equals("景区专项测试") ||
                firstCellValue.equals("公共事务（TSE/QA等）") ||
                firstCellValue.equals("内外公共事物") ||
                firstCellValue.equals("短期借调") ||
                firstCellValue.equals("闲置") ||
                firstCellValue.equals("原生鸿蒙应用测试") ||
                firstCellValue.equals("长期借调") ||
                firstCellValue.equals("法本内部事务") ||
                firstCellValue.equals("共计");
    }

    /**
     * 显示指定人员的数据
     */
    public static void displayPersonData(List<PersonData> personDataList, String personName) {
        for (PersonData person : personDataList) {
            if (person.getUserName().equals(personName)) {
//                System.out.println("序号: " + person.getSerialNumber());
//                System.out.println("城市: " + person.getCity());
//                System.out.println("区域: " + person.getRegion());
//                System.out.println("业务负责人: " + person.getBusinessLeader());
                System.out.println("组员: " + person.getUserName());
//                System.out.println("人员状态: " + person.getPersonStatus());
//                System.out.println("上一个所在业务: " + person.getPreviousBusiness());
//                System.out.println("当前所在业务: " + person.getCurrentBusiness());
//                System.out.println("其他可参与业务: " + person.getOtherBusiness());
//                System.out.println("日期数据:");
                for (Map.Entry<String, String> entry : person.getDateData().entrySet()) {
                    String value = entry.getValue();
                    if (value != null && !value.isEmpty()) {
                        System.out.println("  " + entry.getKey() + ": " + value);
                    }
                }
                return;
            }
        }
        System.out.println("未找到人员: " + personName);
    }

    /**
     * 根据人员姓名获取数据
     */
    public static PersonData getPersonDataByName(List<PersonData> personDataList, String personName) {
        for (PersonData person : personDataList) {
            if (person.getUserName().equals(personName)) {
                return person;
            }
        }
        return null;
    }

    /**
     * 根据日期获取指定人员的工作内容
     */
    public static String getWorkByDate(List<PersonData> personDataList, String personName, String date) {
        PersonData person = getPersonDataByName(personDataList, personName);
        if (person != null) {
            return person.getDateData().getOrDefault(date, "无数据");
        }
        return "人员不存在";
    }
}

